/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package persistence;

import domain.CurrentUser;
import domain.DBConnection;
import domain.Expense;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author admin
 */
public class ExpensePersistence {

    /**
     * 
     * @param expense An Expense object containing the details of the expense to be added
     * @return true if successfully added
     */
    
    public boolean addExpense(Expense expense) {
        boolean status = false;
        String insertSQL = "INSERT INTO `expenses` VALUES (null,'"+expense.getDate()+"', "+expense.getAmount()+", '"+expense.getDescription()+"', '"+expense.getType()+"', '"+expense.getPlace()+"');";
        DBConnection dbConn = DBConnection.getDatabaseConn();
        dbConn.connect();
        status =  dbConn.update(insertSQL);
        if(status){
           String fetchSQL = "SELECT `transaction_id` FROM expenses ORDER BY `transaction_id` DESC LIMIT 1 ";
           ResultSet rs = dbConn.fetch(fetchSQL);
            try {
                if(rs.first()){
                    int transactionId = rs.getInt(1);
                    String insertSQL1 = "INSERT INTO `expenses`.`user-transaction` (`user_id`, `transaction_id`) VALUES ("+CurrentUser.getUser_id()+", "+transactionId+");";
                    status = dbConn.update(insertSQL1);
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return status;

    }
    /**
     * Retrieve all the expense details for the current user
     * @return a Resultset that contains the expense details
     */
    public ResultSet getTableData(){
        String fetchSQL = "SELECT * FROM `expenses`,`user-transaction` WHERE `expenses`.transaction_id = `user-transaction`.transaction_id AND `user-transaction`.user_id = '"+CurrentUser.getUser_id()+"'";
        DBConnection dbConn = DBConnection.getDatabaseConn();
        dbConn.connect();
        ResultSet rs = dbConn.fetch(fetchSQL);
        return  rs;
    }

    /**
     * 
     * @param transaction_id the id of the transaction to be deleted
     * @return true if successfully deleted
     */
    public boolean  deleteExpense(int transaction_id) {
        boolean updatStatus = false;
        String deleteSQL = "DELETE FROM expenses WHERE transaction_id = "+transaction_id+"";
        DBConnection dbConn = DBConnection.getDatabaseConn();
        dbConn.connect();
        updatStatus = dbConn.update(deleteSQL);
        dbConn.disconnect();
        return updatStatus;
        
    }

    /**
     * Updates an expense
     * @param expense An Expense object containing the details to be updated
     * @return true if successfully updated
     */
    public boolean updateExpense(Expense expense) {
        boolean status = false;
        String updateSQL = "UPDATE `expenses` SET `date`='" + expense.getDate() + "', `amount`=" + expense.getAmount() + ",`description`='" + expense.getDescription() + "',`type`='" + expense.getType() + "',`place`='" + expense.getPlace() + "' WHERE `transaction_id` = " + expense.getTransaction_id() + "";
        DBConnection dbConn = DBConnection.getDatabaseConn();
        dbConn.connect();
        status = dbConn.update(updateSQL);
        dbConn.disconnect();
        return status;
    }
            
    
}
